package net.vsame.url2sql.helper;

import java.lang.reflect.Array;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import net.vsame.url2sql.sql.Model;
import net.vsame.url2sql.utils.JdbcUtils;
import net.vsame.url2sql.utils.PageView;

/**
 * </p>Sql工具, ${}中占位符值按优先级排列如下：</p>
 * <ol>
 * <li>从传递MAP取, 当然也可以不传</li>
 * <li>从request.getParameterMap();取</li>
 * <li>从url中rest参数取，注意写法为${0}</li>
 * </ol>
 * <p> Sql Example: select * from student where id in(${ids})</p>
 * 
 * @author <a href="mailto:gaollg@sina.com">Gaollg</a>
 * @data 2013-5-29
 *
 */
public class SqlHelper {
	
	//===============自定义标签====================
	public static interface CustomTag {
		public Object invoke(String key);
	}
	
	private static List<CustomTag> customTags = new ArrayList<SqlHelper.CustomTag>();
	
	public static void add(CustomTag customTag){
		customTags.add(customTag);
	}
	
	private static Object calcCustomTagValue(String key){
		Object o  = null;
		for(CustomTag ct : customTags) {
			o = ct.invoke(key);
			if(o != null){
				break;
			}
		}
		if(o == null){
			throw new RuntimeException("No CustomTag name ~" + key + "!!!");
		}
		return o ;
	}
	
	static {
		add(new CustomTag() {
			@Override
			public Object invoke(String key) {
				if("uuid".equalsIgnoreCase(key)){
					return UUID.randomUUID().toString();
				}
				return null;
			}
		});
	}
	
	///////////////////////////////////////////////开始
	private static class Option{
		String sql;
		Object[] values;
		
		public Option(String sql, Object[] values) {
			this.sql = sql;
			this.values = values;
		}
	}
	
	public static Object[] toObjectArray(Object obj){
		if((obj==null) || !obj.getClass().isArray()){
			return new Object[]{obj};
		}
		int length = Array.getLength(obj);
		Object[] retVal = new Object[length];
		for (int i = 0; i < length; i++) {
			retVal[i] = Array.get(obj, i);
		}
		return retVal;
	}
	
	/**
	 * </p>Sql工具, ${}中占位符值按优先级排列如下：</p>
	 * <ol>
	 * <li>从传递MAP取, 当然也可以不传</li>
	 * <li>从request.getParameterMap();取</li>
	 * <li>从url中rest参数取，注意写法为${0}</li>
	 * </ol>
	 * <p> Sql Example: select * from student where id in(${ids})</p>
	 * 
	 * @param sql
	 * @param userMap
	 * @return
	 */
	public static Option getCommOption(String sql, Object...objects){
		
		Url2SqlContext context = WebHelper.getContext();
		
		Map<String, Object> map = new HashMap<String, Object>();
		//如果Sql长度小于10, 认为是变量名
		if (sql.length() <= 10) {
			sql = context.getUrlConfig().getVar(sql);
		}
		//restful参数
		//form参数
		if(context.getParams() != null){
			map.putAll(context.getParams());
		}
		
		//? 占位符
		StringBuffer placeholderSB = new StringBuffer();
		String placeholderKey = "?KEY&=";//把这些Url关键字全占了, 不信还真有这样的参数名称
		int index = 0;
		char[] array = sql.toCharArray();
		for(int i=0; i<array.length; i++){
			char c = array[i];
			if(c == '?'){
				String temp = placeholderKey + index;
				placeholderSB.append("${").append(temp).append("}");
				map.put(temp, objects[index]);
				index ++;
			}else {
				placeholderSB.append(c);
			}
		}
		
		//开始匹配
		List<Object> values = new ArrayList<Object>();
		Pattern p = Pattern.compile("\\$\\{(.*?)\\}");
		Matcher m = p.matcher(placeholderSB.toString());
		StringBuffer sb = new StringBuffer();
		while(m.find()){
			String key = m.group(1);
			Object[] value = null;
			if(key.toLowerCase().startsWith("session.")){
				//从Session中取
				Object temp = context.getSessionVal(key.substring(8));
				if(temp == null){
					throw new RuntimeException("${" + key + "} in session must exist.");
				}
				value = toObjectArray(temp);
				
			} else if(key.startsWith("~")){
				Object o = calcCustomTagValue(key.substring(1, key.length()));
				value = toObjectArray(o);
			} else{
				if(!map.containsKey(key)){
					throw new RuntimeException("${" + key + "} must exist.");
				}
				value = toObjectArray(map.get(key));
			}
			
			//拼Sql
			StringBuffer temp = new StringBuffer();
			for(Object s: value){
				temp.append("?,");
				values.add(s);
			}
			temp.deleteCharAt(temp.length()-1);
			m.appendReplacement(sb, temp.toString());
		}
		m.appendTail(sb);
		sql = sb.toString();
		
		return new Option(sql, values.toArray());
	}
	
	/**
	 * 查询多个
	 * @param sql
	 * @param map
	 * @return
	 */
	public static List<Model> query(String sql, Object...objects){
		Option o = getCommOption(sql, objects);
		return JdbcUtils.queryList(o.sql, o.values);
	}
	
	/**
	 * 分页
	 * @param currentpage 当前页
	 * @param pagesize 每页显示数
	 * @param column 显示的列
	 * @param where where条件
	 * @param map 自定义参数 
	 * @return
	 */
	public static PageView page(int currentpage, int pagesize, String column, String where, Object...objects){
		Option o = getCommOption(where, objects);
		return JdbcUtils.queryPage(currentpage, pagesize, column, o.sql, o.values);
	}
	
	/**
	 * 
	 * @param column 显示的列
	 * @param where where条件
	 * @param map 自定义参数 
	 * @return
	 */
	public static PageView page(String column, String where, Object...objects){
		Option o = getCommOption(where, objects);
		Url2SqlContext c = WebHelper.getContext();
		Integer currentpage = c.getParamByType(Integer.class, "currentpage"); 
		if(currentpage == null){
			currentpage = 1;
		}
		Integer pagesize = c.getParamByType(Integer.class, "pagesize"); 
		if(pagesize == null){
			pagesize = 10;
		}
		return JdbcUtils.queryPage(currentpage, pagesize, column, o.sql, o.values);
	}
	
	/**
	 * 查询一个
	 * @param sql
	 * @param map
	 * @return
	 */
	public static Model queryOne(String sql, Object...objects){
		Option o = getCommOption(sql, objects);
		return JdbcUtils.queryOne(o.sql, o.values);
	}
	
	/**
	 * 执行Sql，返回影响的条数
	 * @param sql
	 * @param map
	 * @return
	 */
	public static int execute(String sql, Object...objects){
		Option o = getCommOption(sql, objects);
		return JdbcUtils.execute(o.sql, o.values);
	}
	
	/**
	 * 执行Sql，返回自动生成的列，比如主健（GENERATED_KEY），时间
	 * @param sql
	 * @param map
	 * @return
	 */
	public static Long executeGeneratedKey(String sql, Object...objects){
		Option o = getCommOption(sql, objects);
		return JdbcUtils.executeGeneratedKey(o.sql, o.values);
	}
	
	public static List<String> alltables(){
		return JdbcUtils.getAllTables();
	}
	
	
	public static void main(String[] args) {
		WebHelper.init(null, null);
		
		List<Model> a = query("select * from student");
		System.out.println(a);
		
		Url2SqlContext c = WebHelper.getContext();
		Map<String, String[]> m = new HashMap<String, String[]>();
		c.setParams(m);
		m.put("a.id", new String[]{"2"});
		m.put("a.name", new String[]{"gaollg"});
		List<Model> result = query("select * from student where id=${a.id} and name like ?", "gaollg");
		System.out.println(result);
		
		m.clear();
		m.put("ids", new String[]{"1", "2", "3"});
		result = query("select * from student where id in(${ids}) or id in(${~uuid})");
		System.out.println(result);
		
		m.clear();
		m.put("name", new String[]{"Java"});
		m.put("age", new String[]{"44"});
//		long id = executeGeneratedKey("INSERT INTO `student` (`name`, `age`) VALUES ('Huhu', '0')", "");
//		System.out.println(id);
//		int count = execute("INSERT INTO `student` (`name`, `age`) VALUES ('Huhu', '0')", map);
//		System.out.println("count:" + count);
		WebHelper.remove();
	}
	
	public static void main2(String[] args) {
		String a = "a?b?c?d?e?f?ghijklmn?";
		StringBuffer sb = new StringBuffer();
		String key = "?KEY&=";//把这些Url关键字全占了, 不信还真有这样的参数名称
		char[] array = a.toCharArray();
		for(int i=0; i<array.length; i++){
			char c = array[i];
			if(c == '?'){
				sb.append(key + i);
			}else {
				sb.append(c);
			}
		}
		System.out.println(sb);
	}
	
}
